Required auditing parameters for database auditing should be set.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-5685	DG0029-SQLServer9	SV-24075r1_rule	ECAR-1 ECAR-2 ECAR-3	Medium

Description
Auditing provides accountability for changes made to the DBMS configuration or its objects and data. It provides a means to discover suspicious activity and unauthorized changes. Without auditing, a compromise may go undetected and without a means to determine accountability.

STIG	Date
Microsoft SQL Server 2005 Instance Security Technical Implementation Guide	2015-04-03

Details

Check Text ( C-23499r1_chk )
If C2 Auditing is enabled (See Check DM0510: C2 audit mode), this check is Not a Finding. Determine the SQL Server Edition: From the query prompt: SELECT CONVERT(INT, SERVERPROPERTY('EngineEdition')) If value returned is 1 (Personal or Desktop Edition) or 4 (Express Edition), if auditing is not enabled or not configured completely to requirements, review the System Security Plan. If this is properly explained in the System Security Plan, this is Not a Finding. If this is not documented or documented poorly in the System Security Plan, this is a Finding. If value returned is 2 (Standard Edition) or 3 (Enterprise/Developer Edition), these findings apply. Determine if trace is enabled. From the query prompt: SELECT traceid 'TraceID' FROM ::FN_TRACE_GETINFO('0') WHERE traceid <> 1 – Do not count default trace in SQL Server 2005 AND property = 5 AND value = 1 If no trace is returned, this is a Finding. If the trace returned for Check DG0145 is not returned above, this is a Finding.

Check Text ( C-23499r1_chk )

If C2 Auditing is enabled (See Check DM0510: C2 audit mode), this check is Not a Finding.

Determine the SQL Server Edition:

From the query prompt:

SELECT CONVERT(INT, SERVERPROPERTY('EngineEdition'))

If value returned is 1 (Personal or Desktop Edition) or 4 (Express Edition), if auditing is not enabled or not configured completely to requirements, review the System Security Plan. If this is properly explained in the System Security Plan, this is Not a Finding. If this is not documented or documented poorly in the System Security Plan, this is a Finding.

If value returned is 2 (Standard Edition) or 3 (Enterprise/Developer Edition), these findings apply.

Determine if trace is enabled.

From the query prompt:

SELECT traceid 'TraceID'
FROM ::FN_TRACE_GETINFO('0')
WHERE traceid <> 1 – Do not count default trace in SQL Server 2005
AND property = 5
AND value = 1

If no trace is returned, this is a Finding.

If the trace returned for Check DG0145 is not returned above, this is a Finding.

Fix Text (F-23532r1_fix)
Enable the trace created in Check DG0145. From the query prompt: EXEC SP_TRACE_SETSTATUS [TraceID], 1 Replace [TraceID] with the ID of the trace created for the DG0145 audit trace requirement.